﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using Common.SQL;
using System.Data;
using System.Reflection;
using MRSUtil;

namespace MRS.DBL.DAL
{
    public class IndustryDAL
    {
        static log4net.ILog logger = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        /// <summary>
        /// 判断是否存在
        /// </summary>
        public bool Exists(string industryName,int industryId)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            string strSQL = "select count(1) from industry where industryName = @industryName ";
            
            MySqlParameter param1 = new MySqlParameter("@industryName", MySqlDbType.VarChar);
            param1.Value = industryName;

            parameters.Add(param1);
            
            if(industryId>0)
            {
                strSQL += " and id <> @industryId";
                
                MySqlParameter param2 = new MySqlParameter("@industryId", MySqlDbType.Int32);
                param2.Value = industryId;
                parameters.Add(param2); 
			}
            int count = 0;
            IDbConnection conn = DBPool.Instance().GetConnection();
            try
            {
                count = Convert.ToInt32(SQLHelper.ExecuteScalar(conn, CommandType.Text, strSQL, parameters.ToArray()));
            }
            catch (Exception err)
            {
                logger.Error("industry.Exists: " + err);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            return count > 0;
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add( MRS.DBL.Model.IndustryModel model)
        {
            IDbConnection conn = null;
            int effectedrows = 0;

            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("insert into industry(");
            strSQL.Append("IndustryName,status,createTime,CreateUserId");
            strSQL.Append(") values (");
            strSQL.Append("@IndustryName,@status,@createTime,@CreateUserId");
            strSQL.Append(") ");
            strSQL.Append(";select @@IDENTITY");
            MySqlParameter[] parameters = {
			            new MySqlParameter("@IndustryName", MySqlDbType.VarChar,50) ,
                        new MySqlParameter("@status",MySqlDbType.VarChar),
                        new MySqlParameter("@createTime", MySqlDbType.DateTime) ,            
                        new MySqlParameter("@CreateUserId", MySqlDbType.Int32,4)             
              
            };

            parameters[0].Value = model.IndustryName;
            parameters[1].Value = model.Status;
            parameters[2].Value = model.createTime;
            parameters[3].Value = model.CreateUserId;
            conn = DBPool.Instance().GetConnection();

            try
            {
                effectedrows = SQLHelper.Execute(conn, CommandType.Text, strSQL.ToString(), parameters);
            }
            catch (Exception ex)
            {
                logger.Error("industry.Add: " + ex);
                effectedrows = MRSConsts.Code_Error;//System Exception
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            return effectedrows;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update( MRS.DBL.Model.IndustryModel model)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("update industry set ");

            strSQL.Append(" IndustryName = @IndustryName , ");
            strSQL.Append(" createTime = @createTime , ");
            strSQL.Append(" CreateUserId = @CreateUserId,  ");
            strSQL.Append(" Status = @Status ");
            strSQL.Append(" where Id=@Id ");

            MySqlParameter[] parameters = {
			            new MySqlParameter("@Id", MySqlDbType.Int32,4) ,            
                        new MySqlParameter("@IndustryName", MySqlDbType.VarChar,50) ,   
                        new MySqlParameter("@Status", MySqlDbType.VarChar) ,
                        new MySqlParameter("@createTime", MySqlDbType.DateTime) ,            
                        new MySqlParameter("@CreateUserId", MySqlDbType.Int32,4)
            };

            parameters[0].Value = model.Id;
            parameters[1].Value = model.IndustryName;
            parameters[2].Value = model.Status;
            parameters[3].Value = model.createTime;
            parameters[4].Value = model.CreateUserId;

            IDbConnection conn = DBPool.Instance().GetConnection();
            int effectedrows = 0;
            try
            {
                effectedrows = SQLHelper.Execute(conn, CommandType.Text, strSQL.ToString(), parameters);
            }
            catch (Exception ex)
            {
                logger.Error("industry.Update: " + ex);
                effectedrows = MRSConsts.Code_Error;//Exception
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            return effectedrows;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {

            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("delete from industry ");
            strSQL.Append(" where Id=@Id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@Id", MySqlDbType.Int32)
			};
            parameters[0].Value = Id;


            IDbConnection conn = DBPool.Instance().GetConnection();
            int effectedrows = 0;
            try
            {
                effectedrows = SQLHelper.Execute(conn, CommandType.Text, strSQL.ToString(), parameters);
            }
            catch (Exception ex)
            {
                logger.Error("industry.Delete: " + ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            return effectedrows > 0;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public  MRS.DBL.Model.IndustryModel GetModel(int Id)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("select Id, IndustryName,status, createTime, CreateUserId  ");
            strSQL.Append("  from industry ");
            strSQL.Append(" where Id=@Id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@Id", MySqlDbType.Int32)
			};
            parameters[0].Value = Id;


             MRS.DBL.Model.IndustryModel model = null;

            IDbConnection conn = DBPool.Instance().GetConnection();
            DataSet ds = null;

            try
            {
                ds = SQLHelper.QueryDataSet(conn, CommandType.Text, strSQL.ToString(), parameters);
            }
            catch (Exception ex)
            {
                logger.Error("industry.GetModel: " + ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            if (ds.Tables[0].Rows.Count > 0)
            {
                model = new  MRS.DBL.Model.IndustryModel();

                if (ds.Tables[0].Rows[0]["Id"] != null && ds.Tables[0].Rows[0]["Id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["IndustryName"] != null && ds.Tables[0].Rows[0]["IndustryName"].ToString() != "")
                {
                    model.IndustryName = ds.Tables[0].Rows[0]["IndustryName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["status"] != null && ds.Tables[0].Rows[0]["status"].ToString() != "")
                {
                    model.Status = ds.Tables[0].Rows[0]["status"].ToString();
                }
                if (ds.Tables[0].Rows[0]["createTime"] != null && ds.Tables[0].Rows[0]["createTime"].ToString() != "")
                {
                    model.createTime = DateTime.Parse(ds.Tables[0].Rows[0]["createTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CreateUserId"] != null && ds.Tables[0].Rows[0]["CreateUserId"].ToString() != "")
                {
                    model.CreateUserId = int.Parse(ds.Tables[0].Rows[0]["CreateUserId"].ToString());
                }
            }

            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public  MRS.DBL.Model.IndustryModel[] GetList()
        {
            IDbConnection conn = null;
            string strSQL = null;
            DataSet ds = null;
            List< MRS.DBL.Model.IndustryModel> lstModel = null;

            strSQL = "select * FROM industry";

            conn = DBPool.Instance().GetConnection();
            try
            {
                ds = SQLHelper.QueryDataSet(conn, CommandType.Text, strSQL.ToString(), null);
            }
            catch (Exception e)
            {
                logger.Error("industry.GetList:" + e);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            lstModel = new List< MRS.DBL.Model.IndustryModel>();

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                 MRS.DBL.Model.IndustryModel model = new  MRS.DBL.Model.IndustryModel();

                if (ds.Tables[0].Rows[i]["Id"] != null && ds.Tables[0].Rows[i]["Id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[i]["Id"].ToString());
                }
                if (ds.Tables[0].Rows[i]["IndustryName"] != null && ds.Tables[0].Rows[i]["IndustryName"].ToString() != "")
                {
                    model.IndustryName = ds.Tables[0].Rows[i]["IndustryName"].ToString();
                }
                if (ds.Tables[0].Rows[i]["status"] != null && ds.Tables[0].Rows[i]["status"].ToString() != "")
                {
                    model.Status = ds.Tables[0].Rows[i]["status"].ToString();
                }
                if (ds.Tables[0].Rows[i]["createTime"] != null && ds.Tables[0].Rows[i]["createTime"].ToString() != "")
                {
                    model.createTime = DateTime.Parse(ds.Tables[0].Rows[i]["createTime"].ToString());
                }
                if (ds.Tables[0].Rows[i]["CreateUserId"] != null && ds.Tables[0].Rows[i]["CreateUserId"].ToString() != "")
                {
                    model.CreateUserId = int.Parse(ds.Tables[0].Rows[i]["CreateUserId"].ToString());
                }

                lstModel.Add(model);
            }

            return lstModel.ToArray();
        }
    }
}
